package com.scalar.chiptrack.users.notification.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.users.notification.forms.NotificationForm;
import com.scalar.chiptrack.users.notification.Notification;
import com.scalar.chiptrack.users.notification.NotificationEmailInfo;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.adaptor.Adaptor;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * User: KP
 * Date: Sep 3, 2006
 * Time: 5:08:54 PM
*/
public class NotificationDAO
{

    /**
     *  This method loads all notification from Notification_master table and also loads notifications assigned for a particular user from notification_setup_table table
     * @param dbTransaction
     * @param notificationForm
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadNotifications( DBTransaction dbTransaction, NotificationForm notificationForm ) throws ScalarException
    {
        Connection conn         = null;
        ResultSet rs            = null;
        PreparedStatement pstmt = null;
        ArrayList notificationsList     = new ArrayList ();
        Notification notification       = null;
        String userId                   = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("users.notification.loadNotifications") );
            pstmt.setString( 1, notificationForm.getUserId() );
            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                notification = new Notification();
                notification.setNotificationId( rs.getString ( "NM_ID" ) );
                notification.setNotificationName( rs.getString ( "NOTIFICATION_NAME" ) );
                userId = rs.getString ( "USER_ID" );
                if( userId != null && userId.equalsIgnoreCase( notificationForm.getUserId() ) )
                {
                    notification.setSelected( true );
                }
                notificationsList.add( notification );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load notifications from database", sqle );
        }

        finally
        {
            if ( rs != null )
            {
                try { rs.close (); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close (); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return notificationsList;
    }

    /**
     * This method loads all process type in sorted order from  location table
     * @param dbTransaction
     * @return
     * @throws ScalarException
     */
    public static String[] loadAllProcessTypes( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn         = null;
        ResultSet rs            = null;
        PreparedStatement pstmt = null;
        ArrayList processTypesList     = new ArrayList ();
        String[]  processTypesArray    = null;

        try
        {
            if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("users.notification.loadAllProcessTypes") );
            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                processTypesList.add( rs.getString ( "L_TYPE" ) );
            }
            processTypesArray = ( String[] ) processTypesList.toArray( new String[0] );
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load process type from database", sqle );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close (); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close (); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return processTypesArray;
    }

    /**
     * This method loads all process type vendors in sorted order from location table and also load vendors assinged to a partikural user from notification_access_table table
     * @param dbTransaction
     * @param notificationForm
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadAllProcessTypesVendors(  DBTransaction dbTransaction, NotificationForm notificationForm ) throws ScalarException
    {
        Connection conn                 = null;
        ResultSet rs                    = null;
        PreparedStatement pstmt         = null;
        ArrayList allVendorsList        = new ArrayList();
        Notification notification       = null;
        String userId                   = null;


        try
        {
            if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("users.notification.loadAllProcessTypesVendors") );
            pstmt.setString( 1, notificationForm.getUserId() );
            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                notification = new Notification();

                notification.setProcessType( rs.getString ( "L_TYPE")       );
                notification.setLocationId ( rs.getString ( "LOCATION_ID")  );
                notification.setVendorName ( rs.getString ( "L_VENDOR_NAME"));
                userId = rs.getString ( "USER_ID" );
                if( userId != null && userId.equalsIgnoreCase( notificationForm.getUserId() ) )
                {
                    notification.setSelected( true );
                }
                allVendorsList.add( notification );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load notification vendor details from database", sqle );
        }

        finally
        {
            if ( rs != null )
            {
                try { rs.close (); } catch ( SQLException ignore ) { }
            }
           if ( pstmt != null )
            {
                try { pstmt.close (); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return allVendorsList;
    }

    /**
     * This method is used to save notifications selected for a particular user into notification_setup_table table
     * @param dbTransaction
     * @param notificationForm
     * @throws ScalarException
     */
    public static void savedUserNotificationDetails( DBTransaction dbTransaction, NotificationForm notificationForm ) throws ScalarException
    {

        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;

        Notification notification           = null;
        ArrayList selectedNotificationsList = null;
        int notifyListSize                  = 0;

        selectedNotificationsList           = notificationForm.getSelectedNotifications();
        notifyListSize                      = selectedNotificationsList.size();


        try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("users.notification.savedUserNotificationDetails") );

            for ( int i = 0; i < notifyListSize; i++ )
            {
                notification = ( Notification ) selectedNotificationsList.get( i );
                if ( notification.isSelected() )
                {
                    pstmt.setString ( 1, notification.getNotificationId() );
                    pstmt.setString ( 2, notificationForm.getUserId() );
                    pstmt.setString ( 3, notificationForm.getEmailId() );
                    pstmt.addBatch();
                }
            }

            pstmt.executeBatch();
            success = true;
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save User Notification Name Details into database", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}
			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
	    }
    }

    /**
     * This method is used to save vendor selected for a particular user into notification_access_table table
     * @param dbTransaction
     * @param notificationForm
     * @throws ScalarException
     */
    public static void savedUserVendorAccessDetails( DBTransaction dbTransaction, NotificationForm notificationForm ) throws ScalarException
    {

        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;

        Notification notification           = null;
        ArrayList selectedVendorsList       = null;
        int vendorListSize                  = 0;
        selectedVendorsList                 = notificationForm.getVendors();
        vendorListSize                      = selectedVendorsList.size();

        try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("users.notification.savedUserVendorAccessDetails") );

            for ( int i = 0; i < vendorListSize; i++ )
            {
                notification = ( Notification ) selectedVendorsList.get( i );
                if ( notification.isSelected() )
                {
                    pstmt.setString ( 1, notification.getLocationId() );
                    pstmt.setString ( 2, notificationForm.getUserId() );
                    pstmt.setString ( 3, notificationForm.getEmailId() );
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save User Vendor Access Details into database", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}
			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
	    }
    }

    /**
     *  This method is used to delete all notifications assigned for a particular user from notification_setup_table table
     * @param dbTransaction
     * @param notificationForm
     * @throws ScalarException
     */
    public static void deleteUserNotificationDetails( DBTransaction dbTransaction, NotificationForm notificationForm ) throws ScalarException
    {

        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("users.notification.deleteUserNotificationDetails") );
            pstmt.setString ( 1, notificationForm.getUserId() );
            pstmt.executeUpdate();
            success = true;
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not Delete User Notifications Details from database", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}
			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
	    }
    }

    /**
     * This method is used to delete all vendor assigned to a particular user from notification_setup_table table
     * @param dbTransaction
     * @param notificationForm
     * @throws ScalarException
     */
    public static void deleteUserVendorAccessDetails( DBTransaction dbTransaction, NotificationForm notificationForm ) throws ScalarException
    {

        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            //pstmt = conn.prepareStatement ( "DELETE FROM NOTIFICATION_ACCESS_TABLE WHERE USER_ID = ? " );
            pstmt = conn.prepareStatement ( SqlQueryMappings.get("users.notification.deleteUserVendorAccessDetails") );
            pstmt.setString ( 1, notificationForm.getUserId() );
            pstmt.executeUpdate();
            success = true;
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not Delete User Vendor Access Details from database", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}
			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
	    }
    }

    public static ArrayList loadEmailsByNotificationType( DBTransaction dbTransaction, String notificationType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList emails = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT NST.USER_EMAIL FROM NOTIFICATION_MASTER NM, NOTIFICATION_SETUP_TABLE NST WHERE NST.NOTIFICATION_ID = NM.NM_ID AND NM.NOTIFICATION_NAME = ?" );
            pstmt.setString ( 1, notificationType );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                emails.add ( rset.getString ( "USER_EMAIL" ) );
            }

        }
        catch ( SQLException e )
        {
            throw new ScalarException( "Could not load Emails", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ){}
            }
        }
        HashSet set = new HashSet(emails);
        Object[] obj = set.toArray();
        emails.clear();
        emails.addAll(Arrays.asList(obj));
        return emails;
    }

    public static String[] loadEmailsByNSType( String vendorName, String locType, String nsType ) throws Exception
    {
        String[]  emailArray = null;

        try
        {
            NotificationEmailInfo emailInfo = ConfigManager.getNotificationEmailInfo();

            ArrayList  emailsList = emailInfo.getNotificationEmails( vendorName, locType, nsType );
            if ( emailsList != null )
            {
                emailArray = (String[])emailsList.toArray(new String[0]);

                if( ( emailArray != null ) && ( emailArray.length > 0 ) )
                {
                    Arrays.sort( emailArray );
                }
            }
        }
        catch(Exception e)
        {
           System.out.println("Exception while loading the Emails from Data Base");
        }

        return emailArray;
    }

    public static  NotificationEmailInfo getAllNotificationMailIdList() throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        Adaptor adaptor         = null;
        NotificationEmailInfo  emailInfo = new NotificationEmailInfo();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT DISTINCT NSP.USER_EMAIL, L_TYPE, L_VENDOR_NAME, NM.NOTIFICATION_NAME FROM NOTIFICATION_MASTER NM INNER JOIN NOTIFICATION_SETUP_TABLE NSP ON NM.NM_ID = NSP.NOTIFICATION_ID LEFT OUTER JOIN NOTIFICATION_ACCESS_TABLE NAS ON NAS.USER_ID = NSP.USER_ID LEFT OUTER JOIN LOCATION L ON NAS.LOCATION_ID = L.LOCATION_ID" );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                adaptor = new Adaptor();
                adaptor.setEmailID( rset.getString( "USER_EMAIL" ) );
                adaptor.setProcessType( rset.getString( "L_TYPE" ) );
                adaptor.setVendorName( rset.getString( "L_VENDOR_NAME") );
                adaptor.setNotificationName( rset.getString( "NOTIFICATION_NAME" ) );

                emailInfo.addEmails( adaptor );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all New Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return emailInfo;
    }
}
